package com.scalar.chiptrack.commons.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.commons.AttachedFile;
import com.scalar.chiptrack.commons.forms.AttachFileForm;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.FileUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.io.File;

/**
 * User: Sujan
 */
public final class AttachFileDAO {
    public static ArrayList<AttachedFile> getAttachedFileList(DBTransaction dbTransaction, AttachFileForm attachFileForm, boolean includeDeletedFiles) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList<AttachedFile> attchedFileList = new ArrayList<AttachedFile>();
        try {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }
            String query = "SELECT [ATTACH_FILE_ID], [FILE_NAME], [PHYSICAL_FILE_NAME], [FILE_GROUP], [FILE_SUB_GROUP], [COMMENTS], [ATTACHED_BY_USER_ID], [IS_DELETED], [TIMESTAMP] FROM [ATTACH_FILE_TABLE] ";
            query += " WHERE FILE_GROUP='" + attachFileForm.getFileGroup() + "'";
            if (!includeDeletedFiles) {
                query += " AND IS_DELETED=1";
            }
            if (attachFileForm.getFileSubGroup() != null && attachFileForm.getFileSubGroup().trim().length() > 0) {
                query += " AND FILE_SUB_GROUP='" + attachFileForm.getFileSubGroup() + "'";
            }

            pstmt = conn.prepareStatement(query);
            rset = pstmt.executeQuery();
            while (rset.next()) {
                AttachedFile attachedFile = new AttachedFile();
                attachedFile.setId(rset.getInt("ATTACH_FILE_ID"));
                attachedFile.setFileName(rset.getString("FILE_NAME"));
                attachedFile.setPhysicalFileName(rset.getString("PHYSICAL_FILE_NAME"));
                attachedFile.setFileGroup(rset.getString("FILE_GROUP"));
                attachedFile.setFileSubGroup(rset.getString("FILE_SUB_GROUP"));
                attachedFile.setComments(rset.getString("COMMENTS"));
                attachedFile.setAttachedByUserId(rset.getString("ATTACHED_BY_USER_ID"));
                attachedFile.setDeleted(rset.getInt("IS_DELETED") == 0);
                attachedFile.setTimeStamp(rset.getDate("TIMESTAMP"));

                attchedFileList.add(attachedFile);
            }
        }
        catch (Exception e) {
            throw new ScalarException("Could not load Attached Files List", e);
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
        return attchedFileList;
    }

    public static void saveAttachedFile(DBTransaction dbTransaction, AttachedFile attachedFile) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement("INSERT INTO [ATTACH_FILE_TABLE]([FILE_NAME], [PHYSICAL_FILE_NAME], [FILE_GROUP], [FILE_SUB_GROUP], [COMMENTS], [ATTACHED_BY_USER_ID], [IS_DELETED], [TIMESTAMP] ) VALUES ( ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP )");
            pstmt.setString(1, attachedFile.getFileName());
            pstmt.setString(2, attachedFile.getPhysicalFileName());
            pstmt.setString(3, attachedFile.getFileGroup());
            pstmt.setString(4, attachedFile.getFileSubGroup());
            pstmt.setString(5, attachedFile.getComments());
            pstmt.setString(6, attachedFile.getAttachedByUserId());
            pstmt.setInt(7, attachedFile.isDeleted() ? 0 : 1);

            pstmt.executeUpdate();
            if (dbTransaction == null) {
                conn.commit();
            }
            success = true;
        }
        catch (Exception e) {
            throw new ScalarException("Could not update Attached File Details", e);
        }
        finally {
            if (dbTransaction == null && !success && conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }

    public static void deleteAttachedFile(DBTransaction dbTransaction, int id) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }
            pstmt = conn.prepareStatement("UPDATE [ATTACH_FILE_TABLE] SET IS_DELETED = 0 WHERE ATTACH_FILE_ID = ?");
            pstmt.setInt(1, id);

            pstmt.executeUpdate();
            if (dbTransaction == null) {
                conn.commit();
            }
            success = true;
        }
        catch (Exception e) {
            throw new ScalarException("Could not delete Attached File Details", e);
        }
        finally {
            if (dbTransaction == null && !success && conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    public static void updateFileSubGroup(DBTransaction dbTransaction, String filesubgroup, String newfilesubgroup) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;

        try {
            if (dbTransaction != null) {
                conn = dbTransaction.getConnection();
            } else {
                conn = DataSource.getConnection();
            }
            String filegroup = null;
            pstmt = conn.prepareStatement("SELECT [FILE_GROUP] FROM [ATTACH_FILE_TABLE] WHERE [FILE_SUB_GROUP] = ?");
            pstmt.setString(1, filesubgroup);
            rset = pstmt.executeQuery();
            if(rset.next()){
                filegroup = rset.getString("FILE_GROUP");
            }

            String fromDirPath = ConfigManager.getProcessDocFilePath() + File.separator + filegroup;
            if (filesubgroup != null && filesubgroup.trim().length() != 0) {
                fromDirPath += File.separator + filesubgroup;
            }
            String toDirPath = ConfigManager.getProcessDocFilePath() + File.separator + filegroup;
            if (newfilesubgroup != null && newfilesubgroup.trim().length() != 0) {
                toDirPath += File.separator + newfilesubgroup;
            }
            FileUtils.copyDir(fromDirPath, toDirPath);

            rset.close();
            pstmt.close();
            pstmt = conn.prepareStatement("UPDATE [ATTACH_FILE_TABLE] SET [FILE_SUB_GROUP] = ? WHERE [FILE_GROUP] = ? AND [FILE_SUB_GROUP] = ?");
            pstmt.setString(1, newfilesubgroup);
            pstmt.setString(2, filegroup);
            pstmt.setString(3, filesubgroup);

            pstmt.executeUpdate();
            if (dbTransaction == null) {
                conn.commit();
            }
            success = true;
            FileUtils.deleteDir(new File(fromDirPath));
        }
        catch (Exception e) {
            throw new ScalarException("Could not update Attached File Sub Group", e);
        }
        finally {
            if (dbTransaction == null && !success && conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ignore) {
                }
            }
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException ignore) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException ignore) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
}
